﻿using DBUtil.MetaData;
using DBUtil.PostgreSql.MetaData;
using DotNetCommon.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Index = DBUtil.MetaData.Index;

namespace DBUtil.Provider.PostgreSql;

public class PostgreSqlManage : DBManage
{
    public PostgreSqlManage(DBAccess db) : base(db) { }

    #region 旧的
    //    private const string SCHEMA_NOTIN = "'information_schema','pg_catalog','pg_toast_temp_1','pg_temp_1','pg_toast'";

    //    /// <summary>
    //    /// 根据DBAccess创建DBManage
    //    /// </summary>
    //    /// <param name="db"></param>
    //    public PostgreSqlManage(DBAccess db) : base(db) { }

    //    #region 生成insert语句 GeneInsertSql
    //    #endregion

    //    /// <summary>
    //    /// 是否给表填充详细信息,默认不填充,即只有表名称/表说明(注:postgresql中没有记录表创建和修改时间)
    //    /// </summary>
    //    /// <param name="fillTableInfo"></param>
    //    /// <returns></returns>
    //    public override List<Table> ShowTables(bool fillTableInfo = false)
    //    {
    //        if (!fillTableInfo)
    //        {
    //            string sql = $@"
    //select *,(select description from pg_description where objoid in(select oid from pg_class where relname=t.table_name and relkind='r')) as description 
    //from information_schema.tables t
    //where 
    //	table_schema not in({SCHEMA_NOTIN})
    //	and table_type ='BASE TABLE'";
    //            DataTable dt = db.SelectDataTable(sql);
    //            List<Table> tables = new List<Table>();
    //            for (int i = 0, len = dt.Rows.Count; i < len; i++)
    //            {
    //                var tbl = new PostgreSqlTable()
    //                {
    //                    SchemaName = dt.Rows[i]["table_schema"].ToString(),
    //                    Name = dt.Rows[i]["table_name"].ToString(),
    //                    Desc = dt.Rows[i]["description"].ToString(),
    //                    //TODO postgresql不能直接查询表创建时间
    //                    CreateTime = DateTime.MinValue
    //                };
    //                tables.Add(tbl);
    //            }
    //            return tables;
    //        }
    //        else
    //        {
    //            string sql = $@"select * from INFORMATION_SCHEMA.TABLES where table_schema not in({SCHEMA_NOTIN})
    //	and table_type ='BASE TABLE'";
    //            using (db.OpenLongConnectOnce())
    //            {
    //                DataTable dt = db.SelectDataTable(sql);
    //                List<Table> tables = new List<Table>();
    //                for (int i = 0, len = dt.Rows.Count; i < len; i++)
    //                {
    //                    string name = dt.Rows[i]["TABLE_NAME"].ToString();
    //                    var tbl = FillTableInfo(name);
    //                    tables.Add(tbl);
    //                }
    //                return tables;
    //            };
    //        }
    //    }

    //    /// <summary>
    //    /// 返回指定表的详细元数据
    //    /// </summary>
    //    /// <param name="tableName">表名</param>
    //    /// <param name="schemaName">模式名称</param>
    //    public override Table ShowTableDetail(string tableName, string schemaName = null)
    //    {
    //        using (db.OpenLongConnectOnce())
    //        {
    //            var table = new PostgreSqlTable() { Name = tableName };
    //            FillTableInfo(table);
    //            FillColumns(table);
    //            FillTriggers(table);
    //            FillConstraints(table);
    //            FillIndexes(table);
    //            return table;
    //        };
    //    }

    //    /// <summary>
    //    /// 根据表名填充表说明/架构
    //    /// </summary>
    //    /// <param name="table"></param>
    //    private void FillTableInfo(Table table)
    //    {
    //        if (table == null) return;
    //        string sql = $@"
    //select *,(select description from pg_description where objoid in(select oid from pg_class where relname=t.table_name and relkind='r')) as description 
    //from information_schema.tables t
    //where 
    //	table_schema not in({SCHEMA_NOTIN})
    //	and table_type ='BASE TABLE' and t.table_name='{table.Name}'";
    //        DataTable dt = db.SelectDataTable(sql);
    //        if (dt.Rows.Count > 0)
    //        {
    //            table.Name = dt.Rows[0]["table_name"].ToString();
    //            table.Desc = dt.Rows[0]["description"].ToString();
    //            table.SchemaName = dt.Rows[0]["table_schema"].ToString();
    //        }
    //    }

    //    /// <summary>
    //    /// 根据表名填充列信息
    //    /// </summary>
    //    /// <param name="table">要进行填充的表</param>
    //    private void FillColumns(Table table)
    //    {
    //        if (table == null) return;
    //        string sql = $@"
    //select ordinal_position as Colorder,column_name as ColumnName,data_type as TypeName,
    //coalesce(character_maximum_length,numeric_precision,-1) as Length,numeric_scale as Scale,
    //case is_nullable when 'NO' then 0 else 1 end as CanNull,column_default as DefaultVal,
    //case  when position('nextval' in column_default)>0 then 1 else 0 end as IsIdentity, 
    //case when b.pk_name is null then 0 else 1 end as IsPK,c.DeText
    //from information_schema.columns 
    //left join (
    //    select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint  
    //    inner join pg_class on pg_constraint.conrelid = pg_class.oid 
    //    inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and  pg_attr.attnum = pg_constraint.conkey[1] 
    //    inner join pg_type on pg_type.oid = pg_attr.atttypid
    //    where pg_class.relname = '{table.Name}' and pg_constraint.contype='p' 
    //) b on b.colname = information_schema.columns.column_name
    //left join (
    //    select attname,description as DeText from pg_class
    //    left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
    //    left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
    //    where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname='{table.Name}'
    //)c on c.attname = information_schema.columns.column_name
    //where table_schema='public' and table_name='{table.Name}' order by ordinal_position asc
    //";
    //        DataTable dt = db.SelectDataTable(sql);
    //        List<Column> columns = new List<Column>();
    //        if (dt.Rows.Count > 0)
    //        {
    //            for (int i = 0, len = dt.Rows.Count; i < len; i++)
    //            {
    //                var column = new PostgreSqlColumn();
    //                column.Name = dt.Rows[i]["columnname"].ToString();
    //                column.TableName = table.Name;
    //                //列类型
    //                column.TypeString = dt.Rows[i]["typename"].ToString();
    //                column.Length = int.Parse(dt.Rows[i]["length"].ToString());
    //                //列说明
    //                column.Desc = dt.Rows[i]["detext"].ToString();
    //                //是否可空
    //                column.IsNullAble = dt.Rows[i]["cannull"].ToString() == "0" ? false : true;
    //                //自增
    //                column.IsIdentity = dt.Rows[i]["isidentity"].ToString() == "0" ? false : true;
    //                if (column.IsIdentity)
    //                {
    //                    column.IdentityStart = 1;
    //                    column.IdentityIncre = 1;
    //                }
    //                //默认值
    //                var de = dt.Rows[i]["defaultval"].ToString();
    //                column.HasDefault = string.IsNullOrEmpty(de) ? false : true;
    //                if (column.HasDefault) column.Default = de;
    //                columns.Add(column);
    //                //唯一:通过唯一约束修正
    //                //主键
    //                var ispk = dt.Rows[i]["ispk"].ToString();
    //                if (ispk == "1")
    //                {
    //                    table.PrimaryKeyColumns.Add(column);
    //                    column.IsUnique = true;
    //                }
    //            }
    //        }
    //        table.Columns = columns;
    //        if (table.PrimaryKeyColumns.Count > 0)
    //        {
    //            table.PrimaryKey = string.Join(",", table.PrimaryKeyColumns.Select(col => col.Name).ToList());
    //        };
    //    }

    //    /// <summary>
    //    /// 根据表名填充触发器信息
    //    /// </summary>
    //    /// <param name="table">要进行填充的表</param>
    //    private void FillTriggers(Table table)
    //    {
    //        table.Triggers = ShowTableTriggers(table.Name, table.SchemaName);
    //    }

    //    /// <summary>
    //    /// 返回所有的视图(仅用户创建的视图)
    //    /// </summary>
    //    /// <returns></returns>
    //    public override List<View> ShowViews()
    //    {
    //        List<View> views = new List<View>();
    //        var sql = $"select * from pg_views where schemaname not in ({SCHEMA_NOTIN})";
    //        var dt = db.SelectDataTable(sql);
    //        for (int i = 0, len = dt.Rows.Count; i < len; i++)
    //        {
    //            var name = dt.Rows[i]["viewname"].ToString();
    //            var createSql = dt.Rows[i]["definition"].ToString();
    //            var schema = dt.Rows[i]["schemaname"].ToString();
    //            var view = new PostgreSqlView()
    //            {
    //                Name = name,
    //                CreateSql = createSql,
    //                SchemaName = schema
    //            };
    //            views.Add(view);
    //        }
    //        return views;
    //    }

    //    /// <summary>
    //    /// 返回触发器集合
    //    /// </summary>
    //    /// <param name="tableName">所属的表名称</param>
    //    /// <param name="schemaName">模式名称</param>
    //    /// <returns></returns>
    //    public override List<Trigger> ShowTableTriggers(string tableName, string schemaName)
    //    {
    //        if (string.IsNullOrWhiteSpace(tableName)) return new List<Trigger>();
    //        List<Trigger> triggers = new List<Trigger>();
    //        string sql = $@"
    //SELECT *  
    //  FROM information_schema.triggers  
    // WHERE event_object_table='{tableName}' and trigger_schema NOT IN  
    //       ('information_schema','pg_catalog','pg_toast_temp_1','pg_temp_1','pg_toast');   
    //";
    //        DataTable dt = db.SelectDataTable(sql);
    //        for (int i = 0, len = dt.Rows.Count; i < len; i++)
    //        {
    //            string name = dt.Rows[i]["trigger_name"].ToString();
    //            string schema = dt.Rows[i]["trigger_schema"].ToString();
    //            var event_manipulation = dt.Rows[i]["event_manipulation"].ToString();
    //            bool isUpdate = event_manipulation.Contains("UPDATE");
    //            bool isDelete = event_manipulation.Contains("DELETE");
    //            bool isInsert = event_manipulation.Contains("INSERT");
    //            bool isAfter = dt.Rows[i]["action_timing"].ToString() == "AFTER";
    //            var statement = dt.Rows[i]["action_statement"].ToString();
    //            Trigger tri = new PostgreSqlTrigger()
    //            {
    //                Name = name,
    //                CreateSql = statement,
    //                TableName = tableName,
    //                IsInsert = isInsert,
    //                IsUpdate = isUpdate,
    //                IsDelete = isDelete,
    //                IsAfter = isAfter,
    //                IsInsteadof = false,
    //                ActionStatement = statement
    //            };
    //            triggers.Add(tri);
    //        }
    //        return triggers;
    //    }

    //    /// <summary>
    //    /// 根据表名填充约束信息
    //    /// </summary>
    //    /// <param name="table">要进行填充的表</param>
    //    private void FillConstraints(Table table)
    //    {
    //        table.Constraints = ShowTableConstraints(table.Name, table.SchemaName);
    //    }

    //    /// <summary>
    //    /// 返回指定表的所有约束
    //    /// </summary>
    //    /// <param name="tableName">表名</param>
    //    /// <param name="schemaName">模式名称</param>
    //    /// <returns></returns>
    //    public override List<DBUtil.MetaData.Constraint> ShowTableConstraints(string tableName, string schemaName)
    //    {
    //        List<DBUtil.MetaData.Constraint> constraints = new List<DBUtil.MetaData.Constraint>();
    //        if (string.IsNullOrWhiteSpace(tableName)) return constraints;
    //        string sql = $@"
    //SELECT c.conname AS constraint_name,  
    //          CASE c.contype  
    //            WHEN 'c' THEN 'CHECK'  
    //            WHEN 'f' THEN 'FOREIGN KEY'  
    //            WHEN 'p' THEN 'PRIMARY KEY'  
    //            WHEN 'u' THEN 'UNIQUE'  
    //          END AS constraint_type,  
    //          CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,  
    //          CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,  
    //          t.relname AS table_name,  
    //          array_to_string(c.conkey, ' ') AS constraint_key,
    //          CASE confupdtype
    //            WHEN 'a' THEN 'NO ACTION'
    //            WHEN 'r' THEN 'RESTRICT'
    //            WHEN 'c' THEN 'CASCADE'
    //            WHEN 'n' THEN 'SET NULL'
    //            WHEN 'd' THEN 'SET DEFAULT'
    //          END AS on_update,  
    //          CASE confdeltype
    //            WHEN 'a' THEN 'NO ACTION'
    //            WHEN 'r' THEN 'RESTRICT'
    //            WHEN 'c' THEN 'CASCADE'
    //            WHEN 'n' THEN 'SET NULL'
    //            WHEN 'd' THEN 'SET DEFAULT'
    //          END AS on_delete,  
    //          CASE confmatchtype
    //            WHEN 'u' THEN 'UNSPECIFIED'
    //            WHEN 'f' THEN 'FULL'
    //            WHEN 'p' THEN 'PARTIAL'
    //          END AS match_type,  
    //          t2.relname AS references_table,  
    //          array_to_string(c.confkey, ' ') AS fk_constraint_key
    //     FROM pg_constraint c
    //LEFT JOIN pg_class t ON c.conrelid = t.oid
    //LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
    //    WHERE t.relname = '{tableName}'";
    //        var dt = db.SelectDataTable(sql);
    //        for (int i = 0; i < dt.Rows.Count; i++)
    //        {
    //            var constraint = new PostgreSqlConstraint();
    //            constraint.TableName = tableName;
    //            constraint.Name = dt.Rows[i]["constraint_name"].ToString();
    //            constraint.ConstraintKeys = dt.Rows[i]["constraint_key"].ToString();
    //            constraint.Delete_Action = dt.Rows[i]["on_delete"].ToString();
    //            constraint.Update_Action = dt.Rows[i]["on_update"].ToString();
    //            constraints.Add(constraint);
    //            string type = (dt.Rows[i]["constraint_type"] ?? "").ToString();
    //            if (type.StartsWith("CHECK"))
    //            {
    //                constraint.Type = EnumConstraintType.Check;
    //            }
    //            else if (type.StartsWith("DEFAULT"))
    //            {
    //                constraint.Type = EnumConstraintType.DefaultValue;
    //            }
    //            else if (type.StartsWith("FOREIGN"))
    //            {
    //                constraint.Type = EnumConstraintType.ForeignKey;
    //            }
    //            else if (type.StartsWith("PRIMARY KEY"))
    //            {
    //                constraint.Type = EnumConstraintType.PrimaryKey;
    //            }
    //            else if (type.StartsWith("UNIQUE"))
    //            {
    //                constraint.Type = EnumConstraintType.Unique;
    //            }
    //        }
    //        return constraints;
    //    }

    //    /// <summary>
    //    /// 根据表名称填充索引信息
    //    /// </summary>
    //    /// <param name="table"></param>
    //    private void FillIndexes(Table table)
    //    {
    //        table.Indexes = ShowTableIndexes(table.Name, table.SchemaName);
    //    }

    //    /// <summary>
    //    /// 返回指定表所有索引
    //    /// </summary>
    //    /// <param name="tableName">所属的表名称</param>
    //    /// <param name="schemaName">模式名称</param>
    //    /// <returns></returns>
    //    public override List<Index> ShowTableIndexes(string tableName, string schemaName)
    //    {
    //        List<Index> indexs = new List<Index>();
    //        string sql = $@"
    //SELECT t.relname, a.attname, a.attnum  
    //     FROM pg_index c  
    //LEFT JOIN pg_class t  
    //       ON c.indrelid  = t.oid  
    //LEFT JOIN pg_attribute a  
    //       ON a.attrelid = t.oid  
    //      AND a.attnum = ANY(indkey)  
    //    WHERE t.relname = 'testtbl'";
    //        var dt = db.SelectDataTable(sql);
    //        if (dt != null && dt.Rows.Count > 0)
    //        {
    //            for (int i = dt.Rows.Count - 1; i >= 0; i--)
    //            {
    //                Index index = new PostgreSqlIndex();
    //                index.TableName = tableName;
    //                index.ColumnName = dt.Rows[i]["attname"].ToString();
    //                index.Name = tableName + "--" + dt.Rows[i]["attname"].ToString();
    //                indexs.Add(index);
    //            }
    //        }
    //        return indexs;
    //    } 
    #endregion

    public override string AddColumnSql(string tablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string DropColumnSql(string tablePureName, string columnPureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string DropProcedureIfExistSql(string procPureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string DropTableIfExistSql(string tablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string DropTriggerIfExistSql(string triggerPureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> GenerateCodeAsync(string tablePureName, string schemaPureName)
    {
        throw new NotImplementedException();
    }

    public override Task<string> GenerateCreateTableSqlAsync(string tablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task GenerateCreateTableSqlBatchAsync(Stream stream, IEnumerable<string> tableQuotedNames, bool includeInsertSql = false)
    {
        throw new NotImplementedException();
    }

    public override Task<(long exportCount, string sql, long totalCount)> GenerateInsertSqlAsync(string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        throw new NotImplementedException();
    }

    public override Task<long> GenerateInsertSqlAsync(Stream stream, string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        throw new NotImplementedException();
    }

    public override Task<long> GenerateInsertSqlFileAsync(string fileAbsPath, string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> RemoveColumnDefaultSql(string tablePureName, string colPureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string RemovePrimaryKeySql(string tablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string RenameColumnSql(string tablePureName, string oldPureName, string newPureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string RenameTableSql(string oldTablePureName, string newTablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string SetPrimaryKeySql(string tablePureName, string schemaPureName = null, params string[] colPureNames)
    {
        throw new NotImplementedException();
    }

    public override Task<List<Table>> ShowCurrentSchemaTablesAsync()
    {
        throw new NotImplementedException();
    }

    public override Task<List<View>> ShowCurrentSchemaViewsAsync()
    {
        throw new NotImplementedException();
    }

    public override Task<List<Function>> ShowFunctionsAsync(string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<List<Procedure>> ShowProceduresAsync(string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<List<Sequence>> ShowSequencesAsync(string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<List<Column>> ShowTableColumnsAsync(string tablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<List<MetaData.Constraint>> ShowTableConstraintsAsync(string tablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<Table> ShowTableDetailAsync(string tablePureName, string schemaPureName = null, EnumTableDetailType detailType = EnumTableDetailType.All)
    {
        throw new NotImplementedException();
    }

    public override Task<List<Index>> ShowTableIndexesAsync(string tablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<List<Table>> ShowTablesAsync(string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<List<Trigger>> ShowTableTriggersAsync(string tablePureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<View> ShowViewDetailAsync(string viewPureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<List<View>> ShowViewsAsync(string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> UpdateColumnDefaultSql(string tablePureName, string colPureName, string defaultString, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> UpdateColumnDescriptionSql(string tablePureName, string colPureName, string description, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> UpdateColumnIdentitySql(string tablePureName, string colPureName, bool isIdentity, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> UpdateColumnNullAbleSql(string tablePureName, string colPureName, bool isNullAble, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> UpdateColumnSql(string tablePureName, string colPureName, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> UpdateColumnTypeSql(string tablePureName, string colPureName, string typeString, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override Task<string> UpdateColumnUniqueSql(string tablePureName, string colPureName, bool isUnique, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string UpdateConstaintSql()
    {
        throw new NotImplementedException();
    }

    public override string UpdateIndexSql()
    {
        throw new NotImplementedException();
    }

    public override string UpdateTableDescriptionSql(string tablePureName, string description, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }

    public override string UpdateTriggerSql()
    {
        throw new NotImplementedException();
    }

    public override string UpdateViewDescriptionSql(string viewPureName, string description, string schemaPureName = null)
    {
        throw new NotImplementedException();
    }
}
